﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyQuanCaPhe
{
    class connection_sql
    {
        public static SqlConnection Getconnection(TaiKhoan taiKhoan)
        {
            string strcon = "Data Source=.\\sqlexpress;Initial Catalog=QuanLyCaPhe;User ID=" + taiKhoan.Id + "; password= '" + taiKhoan.Pass + "'";
            return new SqlConnection(strcon);
        }
        public static string open(TaiKhoan taiKhoan)
        {
            try
            {
                if (Getconnection(taiKhoan).State == ConnectionState.Closed)
                    Getconnection(taiKhoan).Open();
                return "OK";
            }
            catch (Exception ex)
            {
                return("Error:" + ex.Message);
            }
        }
        public static void close(TaiKhoan taiKhoan)
        {
            try
            {
                if (Getconnection(taiKhoan).State == ConnectionState.Open)
                    Getconnection(taiKhoan).Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:" + ex.Message);
            }
        }


        public static DataSet FillDataSet(string strQuery, CommandType cmdtype, TaiKhoan taiKhoan)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection con = Getconnection(taiKhoan);
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = strQuery;
                cmd.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                da.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:" + ex.Message);
            }
            return ds;
        }
        public static DataSet FillDataSet(string strQuery, CommandType cmdtype, string[] para, string[] values, TaiKhoan taiKhoan)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlConnection con = Getconnection(taiKhoan);
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = strQuery;
                cmd.CommandType = cmdtype;
                cmd.Connection = con;
                SqlParameter sqlpara;
                for (int i = 0; i < para.Length; i++)
                {
                    sqlpara = new SqlParameter();
                    sqlpara.ParameterName = para[i];
                    sqlpara.Value = values[i];
                    cmd.Parameters.Add(sqlpara);
                }
                SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
                sqlda.Fill(ds);
                sqlda.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:", ex.Message);
            }
            return ds;
        }
        public static int Excute(string strQuery, CommandType cmdtype, string[] para, object[] values, TaiKhoan taiKhoan)
        {
            SqlConnection con = Getconnection(taiKhoan);
            con.Open();
            int effectRecord = 0;
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandText = strQuery;
            sqlcmd.Connection = con;
            sqlcmd.CommandType = cmdtype;
            SqlParameter sqlpara;
            for (int i = 0; i < para.Length; i++)
            {
                sqlpara = new SqlParameter();
                sqlpara.ParameterName = para[i];
                sqlpara.SqlValue = values[i];
                sqlcmd.Parameters.Add(sqlpara);
            }
            try
            {
                effectRecord = sqlcmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                if (ex.GetHashCode() == 66337667)
                    MessageBox.Show("Trùng", "Thông báo");
            }
            return effectRecord;
        }

        public static int Excute(string str, TaiKhoan taiKhoan)
        {
            int record = 0;
            try
            {
                SqlConnection sqlconn = Getconnection(taiKhoan);
                sqlconn.Open();
                SqlCommand cmd = new SqlCommand(str, sqlconn);
                record = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return -1;
            }
            return record;
        }
    }
}
